/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2010
 */

package com.tech15.pub.dao;


/**
 * @author 胡作正 email:zuozhenghu@sina.com
 * @version 1.0
 * @since 1.0
 */


import static cn.org.rapid_framework.util.ObjectUtils.isNotEmpty;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javacommon.base.BaseHibernateDao;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import cn.org.rapid_framework.page.Page;
import cn.org.rapid_framework.page.PageRequest;

import com.tech15.pub.model.PubGroupUser;
import com.tech15.pub.vo.query.PubGroupUserQuery;

@Repository
public class PubGroupUserDao extends BaseHibernateDao<PubGroupUser,Long>{

	public Class getEntityClass() {
		return PubGroupUser.class;
	}
	
	public List<PubGroupUser> selectPubGroupUserByPgid(final Long pgid) {
		return (List<PubGroupUser>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(final Session session) throws HibernateException, SQLException {
				String sql = "select a.*,b.realname from PUB_GROUP_USER a left join sys_user b on a.userid=b.userid where a.pgid=:pgid";
				SQLQuery query = (SQLQuery)session.createSQLQuery(sql).setResultTransformer(new AliasToBeanResultTransformer(PubGroupUser.class));
				query.setParameter("pgid",pgid);
				
				query.addScalar("pguid",Hibernate.LONG);
				query.addScalar("pgid",Hibernate.LONG);
				query.addScalar("userid",Hibernate.LONG);
				query.addScalar("realName",Hibernate.STRING);
				
				return query.list();
			}
		});
	}
	
	public Page findByPageRequest(PageRequest<Map> pageRequest) {
		//XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符. 以下为图方便采用sql拼接,适用性能要求不高的应用,使用占位符方式可以优化性能. 
		// [column] 为PageRequest.getFilters()中的key
		String sql = "select t from PubPubGroupUser t where 1=1 "
				+ "/~ and t.pgid = '[pgid]' ~/"
				+ "/~ and t.userid = '[userid]' ~/"
				+ "/~ order by [sortColumns] ~/";
		return pageQuery(sql,pageRequest);
	}
	
	public Page findPage(PubGroupUserQuery query) {
		String sql = "select t.*,b.username,b.realname from PUB_GROUP_USER t,sys_user b where t.userid=b.userid  ";
		StringBuilder sql2 = new StringBuilder(sql);
		if(isNotEmpty(query.getPgid())) {
            sql2.append(" and  t.pgid = :pgid ");
        }
		Map map = new HashMap();
		map.put("pguid", "Long");
		map.put("pgid", "Long");
		map.put("userid", "Long");
		map.put("realName", "String");
		map.put("username", "String");
		return pageSqlQuery(sql2.toString(),query,map,PubGroupUser.class);
	}
	
	public List<PubGroupUser> checkUser(){
		String sql = "select t.*,t.auth_date as authDate,t1.realname,t2.groupname from PUB_GROUP_USER t left join sys_user_info t1 on t.userid=t1.userid left join sys_privilege_group t2 on t.pgid=t2.pgid where t.is_confirm=0";
		Map map = new HashMap();
		map.put("pguid","Long");
		map.put("pgid","Long");
		map.put("realName","String");
		map.put("applicant","String");
		map.put("groupName","String");
		map.put("authDate", "Timestamp");
		return findSqlAll(sql, map, PubGroupUser.class);
	}
	

}
